﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Ex7.AddNewProduct
{
    public class AddNewProduct
    {
        private SqlConnection dbCon;

        public static void Main()
        {
            AddNewProduct newProduct = new AddNewProduct();
            try
            {
                newProduct.ConnectToDB();

                int newProjectId = newProduct.InsertProduct("Telerik", 1);
                Console.WriteLine("Project with ID={0} was added!",newProjectId);
            }
            finally
            {
                newProduct.DisconnectFromDB();
            }
        }

        private void ConnectToDB()
        {
            dbCon = new SqlConnection(Settings.Default.DBConnectionString);
            dbCon.Open();
        }

        private void DisconnectFromDB()
        {
            if (dbCon != null)
            {
                dbCon.Close();
            }
        }

        public int InsertProduct(string productName,int discontinued, int? supplierID = null, 
            int? categoryID=null, string quantityPerUnit = null, decimal? unitPrice = null,
            short? unitsInStock = null, short? unitsOnOrder = null, short? reorderLevel = null)
        {
            SqlCommand cmdInsertProduct = new SqlCommand(
                @"INSERT INTO Products(ProductName,Discontinued,SupplierID,CategoryID,QuantityPerUnit,
                  UnitPrice,UnitsInStock, UnitsOnOrder ,ReorderLevel)  
                  VALUES (@productName,@discontinued,@supplierID,@categoryID,@quantityPerUnit,@unitPrice,
                            @unitsInStock,@unitsOnOrder,@reorderLevel)", dbCon);
            cmdInsertProduct.Parameters.Add(new SqlParameter("@productName", productName));
            cmdInsertProduct.Parameters.Add(new SqlParameter("@discontinued", discontinued));

            SqlParameter sqlParameterSupplierID = new SqlParameter("@supplierID", supplierID);
            if (supplierID == null)
            {
                sqlParameterSupplierID.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterSupplierID);

            SqlParameter sqlParameterCategoryID = new SqlParameter("@categoryID", categoryID);
            if (categoryID == null)
            {
                sqlParameterCategoryID.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterCategoryID);

            SqlParameter sqlParameterQuantityPerUnit = new SqlParameter("@quantityPerUnit", quantityPerUnit);
            if (quantityPerUnit == null)
            {
                sqlParameterQuantityPerUnit.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterQuantityPerUnit);

            SqlParameter sqlParameterUnitPrice = new SqlParameter("@unitPrice", unitPrice);
            if (unitPrice == null)
            {
                sqlParameterUnitPrice.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterUnitPrice);

            SqlParameter sqlParameterUnitsInStock = new SqlParameter("@unitsInStock", unitsInStock);
            if (unitsInStock == null)
            {
                sqlParameterUnitsInStock.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterUnitsInStock);

            SqlParameter sqlParameterUnitsOnOrder = new SqlParameter("@unitsOnOrder", unitsOnOrder);
            if (unitsOnOrder == null)
            {
                sqlParameterUnitsOnOrder.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterUnitsOnOrder);

            SqlParameter sqlParameterReorderLevel = new SqlParameter("@reorderLevel", reorderLevel);
            if (reorderLevel == null)
            {
                sqlParameterReorderLevel.Value = DBNull.Value;
            }
            cmdInsertProduct.Parameters.Add(sqlParameterReorderLevel);

            cmdInsertProduct.ExecuteNonQuery();

            SqlCommand cmdSelectIdentity =
                new SqlCommand("SELECT @@Identity", dbCon);
            int insertedRecordId =
                (int)(decimal)cmdSelectIdentity.ExecuteScalar();
            return insertedRecordId;
        }
    }
}
